In even a recordable database such as PostgreSQL, it is desirable to completely erase (VACUUM) a tuple (row) with an elapse of a predetermined period of time after deleting the tuple in order to effectively utilize resources.
A transaction is, however, managed with a single snapshot from a start (BEGIN) to an end (COMMIT or ROLLBACK) of implementation, and hence, even when a specified tuple is deleted during a certain transaction, this does not necessarily mean that none of the reference to this tuple is promptly made from within all the transactions. Therefore, a principle is that the tuple is not completely erased unless a series of transactions during which to make reference to the tuple are all committed (COMMIT). Even if the tuple is deleted, it is to be contrived that the reference to this tuple can be made on other implementation-underway snapshots at that point of time.
PostgreSQL therefore adopts a mechanism for determining where is delimited for the complete deletion (VACUUM) while referring to transaction IDs of the transactions that have already been committed as viewed from the snapshots of the respective transactions by use of the transaction ID attached to each transaction. Namely, it is because of an operation of calculating respective minimum values in the transaction IDs of the transactions deemed to be the implementation-underway transactions in the snapshots of the respective transactions and performing the complete deletion in accordance with the transaction ID having the smallest of the minimum values.
[Patent document 1] Japanese Patent Application Laid-Open Publication No. 2006-293910